﻿using System;
using System.Data.SqlServerCe;
using System.Diagnostics.CodeAnalysis;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using SenseTimeTracker.Misc;

namespace SenseTimeTracker.BusinessLayer
{
	/// <summary>
	/// Class implements the time record
	/// </summary>
	public class TimeCard : IColumnSortable
	{
		/// <summary>
		/// Field contains the name of the task id column
		/// </summary>
		public const string IdColumnName = "timeRecordId";

		/// <summary>
		/// Field contains the name of the person id column
		/// </summary>
		public const string PersonIdColumnName = "personId";

		/// <summary>
		/// Field contains the name of the task id column
		/// </summary>
		public const string TaskIdColumnName = "taskId";

		/// <summary>
		/// Field contains the time record table name
		/// </summary>
		public const string TableName = "TimeRecords";

		private bool newCard = true;
		private Guid id = Guid.NewGuid();
		private Guid client = Persistance.Instance.SelectedClient;
		private Guid project = Persistance.Instance.SelectedProject;
		private Guid task = Persistance.Instance.SelectedTask;
		private string description = string.Empty;
		private string details = string.Empty;
		private DateTime startDateTime = Persistance.Instance.DefaultStartTime;
		private DateTime finalDateTime = Persistance.Instance.DefaultFinalTime;

		public string Name
		{
			get { return string.Empty; }
		}

		public string Type
		{
			get { return string.Empty; }
		}

		public string Number
		{
			get { return string.Empty; }
		}

		public TimeSpan Duration
		{
			get { return finalDateTime - startDateTime; }
		}

		public DateTime Date
		{
			get { return startDateTime; }
		}

		public Guid Id
		{
			get { return id; }
			set { id = value; }
		}

		[SuppressMessage("Microsoft.StyleCop.CSharp.ReadabilityRules", "SA1107:CodeMustNotContainMultipleStatementsOnOneLine", Justification = "can contain serialize")]
		public Guid Client
		{
			get { return client; }
			set { client = value; Persistance.Instance.SelectedClient = value; }
		}

		[SuppressMessage("Microsoft.StyleCop.CSharp.ReadabilityRules", "SA1107:CodeMustNotContainMultipleStatementsOnOneLine", Justification = "can contain serialize")]
		public Guid Project
		{
			get { return project; }
			set { project = value; Persistance.Instance.SelectedProject = value; }
		}

		[SuppressMessage("Microsoft.StyleCop.CSharp.ReadabilityRules", "SA1107:CodeMustNotContainMultipleStatementsOnOneLine", Justification = "can contain serialize")]
		public Guid Task
		{
			get { return task; }
			set { task = value; Persistance.Instance.SelectedTask = value; }
		}

		public string Description
		{
			get { return description; }
			set { description = value; }
		}

		public string Details
		{
			get { return details; }
			set { details = value; }
		}

		public DateTime StartDateTime
		{
			get { return startDateTime; }
			set { startDateTime = value; }
		}

		public DateTime FinalDateTime
		{
			get { return finalDateTime; }
			set { finalDateTime = value; }
		}

		public bool NewCard
		{
			get { return newCard; }
			set { newCard = value; }
		}

		public static TimeCard GetTimeCard(Guid id)
		{
			TimeCard timeCard = null;
			SqlCeCommand command = new SqlCeCommand(
					"SELECT " +
					"timeRecordStartTime, timeRecordFinalTime, description, details, Clients.clientId, Projects.projectId, Tasks.taskId " +
					"FROM TimeRecords " +
					"JOIN Tasks ON TimeRecords.taskId = Tasks.taskId " +
					"JOIN Projects ON Tasks.projectId = Projects.projectId " +
					"JOIN Clients ON Projects.clientId = Clients.clientId " +
					"WHERE timeRecordId=@timeRecordId",
					DatabaseManager.DbConnection);

			command.Parameters.Add("@timeRecordId", id);
			DatabaseManager.DbConnection.Open();
			SqlCeDataReader reader = command.ExecuteReader();

			if (reader.Read())
			{
				timeCard = new TimeCard
       	{
					Id = id,
					Client = (Guid)reader["clientId"],
					Project = (Guid)reader["projectId"],
					Task = (Guid)reader["taskId"],
					StartDateTime = (DateTime)reader["timeRecordStartTime"],
					FinalDateTime = (DateTime)reader["timeRecordFinalTime"],
					Description = reader["description"].ToString(),
					Details = reader["details"].ToString(),
					NewCard = false,
				};
			}

			DatabaseManager.DbConnection.Close();
			return timeCard;
		}

		public static DateTime LatestTime()
		{
			DateTime latestCardToday = Persistance.Instance.DefaultStartTime;
			SqlCeCommand command = new SqlCeCommand(
				"SELECT timeRecordStartTime, timeRecordFinalTime FROM TimeRecords WHERE timeRecordFinalTime >= @today ORDER BY timeRecordStartTime ASC",
				DatabaseManager.DbConnection);
			command.Parameters.Add("today", latestCardToday);
			DatabaseManager.DbConnection.Open();
			SqlCeDataReader reader = command.ExecuteReader();
			while (reader.Read())
			{
				DateTime timeCardStartTime = (DateTime)reader["timeRecordStartTime"];
				DateTime timeCardFinalTime = (DateTime)reader["timeRecordFinalTime"];
				if (timeCardStartTime >= DateTime.Now.Date && timeCardFinalTime < DateTime.Now.Date + TimeSpan.FromDays(1))
				{
					latestCardToday = timeCardFinalTime;
				}
			}

			DatabaseManager.DbConnection.Close();
			return latestCardToday;
		}

		/// <summary>
		/// Updates a time card
		/// </summary>
		public void Update()
		{
			SqlCeCommand command = new SqlCeCommand(
					"UPDATE " + TableName + " SET " +
					"taskId = @taskId, " +
					"description = @description, " +
					"details = @details, " +
					"timeRecordStartTime = @timeRecordStartTime, " +
					"timeRecordFinalTime = @timeRecordFinalTime " +
					"WHERE timeRecordId = @timeRecordId",
					DatabaseManager.DbConnection);

			command.Parameters.Add(IdColumnName, id);
			command.Parameters.Add("@taskId", task);
			command.Parameters.Add("@description", description);
			command.Parameters.Add("@details", details);
			command.Parameters.Add("@timeRecordStartTime", startDateTime);
			command.Parameters.Add("@timeRecordFinalTime", finalDateTime);
			DatabaseManager.DbConnection.Open();
			command.ExecuteNonQuery();
			DatabaseManager.DbConnection.Close();
		}

    public void Insert()
    {
			SqlCeCommand command = new SqlCeCommand(
					"INSERT INTO TimeRecords " +
					"(timeRecordId, taskId, personId, description, details, timeRecordStartTime, timeRecordFinalTime) " +
					"VALUES(@timeRecordId, @taskId, @personId, @description, @details, @timeRecordStartTime, @timeRecordFinalTime)",
					DatabaseManager.DbConnection);

			command.Parameters.Add("@timeRecordId", id);
			command.Parameters.Add("@taskId", task);
			command.Parameters.Add("@personId", Persistance.Instance.UserId);
			command.Parameters.Add("@description", description);
			command.Parameters.Add("@details", details);
			command.Parameters.Add("@timeRecordStartTime", startDateTime);
			command.Parameters.Add("@timeRecordFinalTime", finalDateTime);
			DatabaseManager.DbConnection.Open();
			command.ExecuteNonQuery();
			DatabaseManager.DbConnection.Close();
		}

		public static  void Delete(Guid id)
		{
			SqlCeCommand command = new SqlCeCommand(
					"DELETE FROM TimeRecords " +
					"WHERE timeRecordId = @timeRecordId",
					DatabaseManager.DbConnection);

			command.Parameters.Add("@timeRecordId", id);
			DatabaseManager.DbConnection.Open();
			command.ExecuteNonQuery();
			DatabaseManager.DbConnection.Close();
		}

		public static Guid Copy(Guid oldTimeCardId)
		{
			Guid newTimeCardId = Guid.NewGuid();
			SqlCeCommand command = new SqlCeCommand(
				"INSERT INTO TimeRecords " +
				"(timeRecordId, taskId, personId, description, details, timeRecordStartTime, timeRecordFinalTime) " +
				"SELECT @newTimeRecordId, taskId, personId, description, details, timeRecordStartTime, timeRecordFinalTime " +
				"FROM TimeRecords " +
				"WHERE timeRecordId = @oldTimeRecordId",
				DatabaseManager.DbConnection);

			command.Parameters.Add("@newTimeRecordId", newTimeCardId);
			command.Parameters.Add("@oldTimeRecordId", oldTimeCardId);
			DatabaseManager.DbConnection.Open();
			command.ExecuteNonQuery();
			DatabaseManager.DbConnection.Close();
			return newTimeCardId;
		}
	}
}
